package com.example.managemyself.data.database;

import android.content.Context;
import android.util.Log;
import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;
import androidx.room.TypeConverters;
import androidx.room.migration.Migration;
import androidx.sqlite.db.SupportSQLiteDatabase;
import androidx.annotation.NonNull;

import com.example.managemyself.data.model.Task;
import com.example.managemyself.data.model.DiaryEntry;
import com.example.managemyself.data.model.HealthData;
import com.example.managemyself.data.model.ExerciseLog;
import com.example.managemyself.data.model.PomodoroSession;
import com.example.managemyself.data.model.UserApiKey;
import com.example.managemyself.data.model.User;
// import com.example.managemyself.data.model.AIConversation;
// import com.example.managemyself.data.model.AIMessage;
import com.example.managemyself.model.UserSettings;
import com.example.managemyself.util.DateConverter;

@Database(
    entities = {
        Task.class, 
        DiaryEntry.class, 
        HealthData.class, 
        ExerciseLog.class, 
        PomodoroSession.class,
        UserApiKey.class,
        User.class,
        // AIConversation.class,
        // AIMessage.class,
        UserSettings.class
    },
    version = 13,
    exportSchema = true
)
@TypeConverters({DateConverter.class})
public abstract class AppDatabase extends RoomDatabase {
    
    private static final String DATABASE_NAME = "manage_myself_db";
    private static volatile AppDatabase INSTANCE;
    
    // 定义DAO抽象方法
    public abstract TaskDao taskDao();
    public abstract DiaryEntryDao diaryEntryDao();
    public abstract UserSettingsDao userSettingsDao();
    public abstract HealthDao healthDao();
    public abstract UserApiKeyDao userApiKeyDao();
    public abstract UserDao userDao();
    // public abstract AIConversationDao aiConversationDao();
    // public abstract AIMessageDao aiMessageDao();
    
    static final Migration M_4_5 = new Migration(4, 5) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("ALTER TABLE tasks ADD COLUMN completedDate INTEGER");
        }
    };
    
    static final Migration M_5_6 = new Migration(5, 6) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 删除AI相关表（如果存在）
            database.execSQL("DROP TABLE IF EXISTS `ai_conversations`");
            database.execSQL("DROP TABLE IF EXISTS `ai_messages`");
            // 版本6：移除AI功能相关表
            Log.d("AppDatabase", "Migration 5->6: Removed AI tables");
        }
    };
    
    static final Migration M_6_7 = new Migration(6, 7) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 为diary_entries表添加createdDate和updatedDate字段
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN createdDate INTEGER");
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN updatedDate INTEGER");
            
            // 为现有记录设置默认值（当前时间）
            long currentTime = System.currentTimeMillis();
            database.execSQL("UPDATE diary_entries SET createdDate = " + currentTime + " WHERE createdDate IS NULL");
            database.execSQL("UPDATE diary_entries SET updatedDate = " + currentTime + " WHERE updatedDate IS NULL");
            
            Log.d("AppDatabase", "Migration 6->7: Added createdDate and updatedDate to diary_entries");
        }
    };
    
    // 从版本3直接到版本7的迁移（处理跳跃升级）
    static final Migration M_3_7 = new Migration(3, 7) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 1. 添加tasks表的completedDate字段（版本4->5的变更）
            database.execSQL("ALTER TABLE tasks ADD COLUMN completedDate INTEGER");
            
            // 2. 删除AI相关表（版本5->6的变更）
            database.execSQL("DROP TABLE IF EXISTS `ai_conversations`");
            database.execSQL("DROP TABLE IF EXISTS `ai_messages`");
            
            // 3. 为diary_entries表添加createdDate和updatedDate字段（版本6->7的变更）
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN createdDate INTEGER");
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN updatedDate INTEGER");
            
            // 4. 为现有记录设置默认值
            long currentTime = System.currentTimeMillis();
            database.execSQL("UPDATE diary_entries SET createdDate = " + currentTime + " WHERE createdDate IS NULL");
            database.execSQL("UPDATE diary_entries SET updatedDate = " + currentTime + " WHERE updatedDate IS NULL");
            
            Log.d("AppDatabase", "Migration 3->7: Complete migration from version 3 to 7");
        }
    };
    
    // 从版本4直接到版本7的迁移
    static final Migration M_4_7 = new Migration(4, 7) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 1. 添加tasks表的completedDate字段（版本4->5的变更）
            database.execSQL("ALTER TABLE tasks ADD COLUMN completedDate INTEGER");
            
            // 2. 删除AI相关表（版本5->6的变更）
            database.execSQL("DROP TABLE IF EXISTS `ai_conversations`");
            database.execSQL("DROP TABLE IF EXISTS `ai_messages`");
            
            // 3. 为diary_entries表添加createdDate和updatedDate字段（版本6->7的变更）
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN createdDate INTEGER");
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN updatedDate INTEGER");
            
            // 4. 为现有记录设置默认值
            long currentTime = System.currentTimeMillis();
            database.execSQL("UPDATE diary_entries SET createdDate = " + currentTime + " WHERE createdDate IS NULL");
            database.execSQL("UPDATE diary_entries SET updatedDate = " + currentTime + " WHERE updatedDate IS NULL");
            
            Log.d("AppDatabase", "Migration 4->7: Complete migration from version 4 to 7");
        }
    };
    
    // 从版本5直接到版本7的迁移
    static final Migration M_5_7 = new Migration(5, 7) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 1. 删除AI相关表（版本5->6的变更）
            database.execSQL("DROP TABLE IF EXISTS `ai_conversations`");
            database.execSQL("DROP TABLE IF EXISTS `ai_messages`");
            
            // 2. 为diary_entries表添加createdDate和updatedDate字段（版本6->7的变更）
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN createdDate INTEGER");
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN updatedDate INTEGER");
            
            // 3. 为现有记录设置默认值
            long currentTime = System.currentTimeMillis();
            database.execSQL("UPDATE diary_entries SET createdDate = " + currentTime + " WHERE createdDate IS NULL");
            database.execSQL("UPDATE diary_entries SET updatedDate = " + currentTime + " WHERE updatedDate IS NULL");
            
            Log.d("AppDatabase", "Migration 5->7: Complete migration from version 5 to 7");
        }
    };
    
    static final Migration M_7_8 = new Migration(7, 8) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 添加UserSettings表的新字段
            try {
                database.execSQL("ALTER TABLE user_settings ADD COLUMN dataRetention TEXT");
                database.execSQL("ALTER TABLE user_settings ADD COLUMN biometricEnabled INTEGER NOT NULL DEFAULT 0");
                database.execSQL("ALTER TABLE user_settings ADD COLUMN autoLockTime TEXT");
                database.execSQL("ALTER TABLE user_settings ADD COLUMN weatherApiKey TEXT");
                database.execSQL("ALTER TABLE user_settings ADD COLUMN aiApiKey TEXT");
                database.execSQL("ALTER TABLE user_settings ADD COLUMN almanacApiKey TEXT");
                
                // 为现有记录设置默认值
                database.execSQL("UPDATE user_settings SET dataRetention = '1year' WHERE dataRetention IS NULL");
                database.execSQL("UPDATE user_settings SET autoLockTime = '5min' WHERE autoLockTime IS NULL");
                database.execSQL("UPDATE user_settings SET weatherApiKey = '' WHERE weatherApiKey IS NULL");
                database.execSQL("UPDATE user_settings SET aiApiKey = '' WHERE aiApiKey IS NULL");
                database.execSQL("UPDATE user_settings SET almanacApiKey = '' WHERE almanacApiKey IS NULL");
                
                Log.d("AppDatabase", "Migration 7->8: Added new fields to user_settings");
            } catch (Exception e) {
                Log.e("AppDatabase", "Migration 7->8 failed, will fallback to destructive migration", e);
                // 如果迁移失败，将使用fallbackToDestructiveMigration
            }
        }
    };
    
    static final Migration M_8_9 = new Migration(8, 9) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 创建用户API密钥表
            database.execSQL("CREATE TABLE IF NOT EXISTS `user_api_keys` (" +
                "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "`userId` TEXT, " +
                "`apiType` TEXT, " +
                "`apiKey` TEXT, " +
                "`apiName` TEXT, " +
                "`isActive` INTEGER NOT NULL, " +
                "`createdDate` INTEGER, " +
                "`updatedDate` INTEGER, " +
                "`description` TEXT)");
            
            Log.d("AppDatabase", "Migration 8->9: Created user_api_keys table");
        }
    };
    
    static final Migration M_9_10 = new Migration(9, 10) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 创建用户表
            database.execSQL("CREATE TABLE IF NOT EXISTS `users` (" +
                "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "`username` TEXT NOT NULL, " +
                "`email` TEXT NOT NULL, " +
                "`password` TEXT, " +
                "`nickname` TEXT, " +
                "`avatar` TEXT, " +
                "`role` TEXT NOT NULL, " +
                "`createdDate` INTEGER, " +
                "`lastLoginDate` INTEGER, " +
                "`isActive` INTEGER NOT NULL)");
                
            Log.d("AppDatabase", "Migration 9->10: Created users table");
        }
    };
    
    static final Migration M_10_11 = new Migration(10, 11) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 为用户表添加扩展的个人资料字段，先检查列是否已存在
            try {
                // 检查gender列是否已存在
                android.database.Cursor cursor = database.query("PRAGMA table_info(users)");
                boolean genderExists = false;
                boolean phoneExists = false;
                boolean birthdayExists = false;
                boolean bioExists = false;
                boolean constellationExists = false;
                boolean hobbiesExists = false;
                boolean occupationExists = false;
                boolean cityExists = false;
                boolean publicBirthdayExists = false;
                boolean publicContactExists = false;
                
                if (cursor != null) {
                    while (cursor.moveToNext()) {
                        String columnName = cursor.getString(1); // column_name is at index 1
                        if ("gender".equals(columnName)) genderExists = true;
                        if ("phone".equals(columnName)) phoneExists = true;
                        if ("birthday".equals(columnName)) birthdayExists = true;
                        if ("bio".equals(columnName)) bioExists = true;
                        if ("constellation".equals(columnName)) constellationExists = true;
                        if ("hobbies".equals(columnName)) hobbiesExists = true;
                        if ("occupation".equals(columnName)) occupationExists = true;
                        if ("city".equals(columnName)) cityExists = true;
                        if ("publicBirthday".equals(columnName)) publicBirthdayExists = true;
                        if ("publicContact".equals(columnName)) publicContactExists = true;
                    }
                    cursor.close();
                }
                
                // 只添加不存在的列
                if (!phoneExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN phone TEXT");
                }
                if (!birthdayExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN birthday INTEGER");
                }
                if (!genderExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN gender TEXT");
                }
                if (!bioExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN bio TEXT");
                }
                if (!constellationExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN constellation TEXT");
                }
                if (!hobbiesExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN hobbies TEXT");
                }
                if (!occupationExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN occupation TEXT");
                }
                if (!cityExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN city TEXT");
                }
                if (!publicBirthdayExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN publicBirthday INTEGER NOT NULL DEFAULT 0");
                }
                if (!publicContactExists) {
                    database.execSQL("ALTER TABLE users ADD COLUMN publicContact INTEGER NOT NULL DEFAULT 0");
                }
                
                Log.d("AppDatabase", "Migration 10->11: Safely added extended profile fields to users table");
            } catch (Exception e) {
                Log.e("AppDatabase", "Migration 10->11 failed: " + e.getMessage(), e);
                // 如果迁移失败，记录错误但不崩溃
            }
        }
    };
    
    static final Migration M_11_12 = new Migration(11, 12) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 为diary_entries表添加userId字段
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN userId INTEGER NOT NULL DEFAULT 1");
            
            Log.d("AppDatabase", "Migration 11->12: Added userId field to diary_entries table");
        }
    };
    
    static final Migration M_12_13 = new Migration(12, 13) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 为diary_entries表添加多媒体附件支持字段
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN attachmentPaths TEXT");
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN attachmentTypes TEXT");
            
            // 为diary_entries表添加完成任务记录字段
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN completedTaskIds TEXT");
            database.execSQL("ALTER TABLE diary_entries ADD COLUMN completedTasksSummary TEXT");
            
            Log.d("AppDatabase", "Migration 12->13: Added attachment and completed tasks fields to diary_entries table");
        }
    };
    
    // 单例模式获取数据库实例
    public static AppDatabase getInstance(Context context) {
        if (INSTANCE == null) {
            synchronized (AppDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(
                            context.getApplicationContext(),
                            AppDatabase.class,
                            DATABASE_NAME)
                            .addMigrations(M_4_5, M_5_6, M_6_7, M_3_7, M_4_7, M_5_7, M_7_8, M_8_9, M_9_10, M_10_11, M_11_12, M_12_13)
                            .fallbackToDestructiveMigration()
                            .build();
                }
            }
        }
        return INSTANCE;
    }
    
    /**
     * 清理数据库实例（用于解决版本冲突）
     */
    public static synchronized void clearInstance() {
        if (INSTANCE != null) {
            INSTANCE.close();
            INSTANCE = null;
            Log.d("AppDatabase", "Database instance cleared");
        }
    }
    
    // 为了向后兼容，保留getDatabase方法
    public static AppDatabase getDatabase(Context context) {
        return getInstance(context);
    }
}
